﻿Imports System.Data
Imports System.Data.SqlClient
Imports DevExpress.XtraEditors.Controls
Imports System.IO
Imports System.Net.Mail
Imports System.Threading
Imports System.Globalization

Module moPublicFunction
    Dim constatus As Boolean = False
    Dim con As SqlConnection
    Public Myresources As System.Resources.ResourceManager = New System.Resources.ResourceManager(GetType(myResource))

    Public Sub LoadCompanyProfile()
        Dim str As String = "SELECT CompanyName, Address1, Address2, Address3, Address4, Telephone, Fax, Email,Website, Remark, ReceiptFooter  FROM CompanyProfile WHERE ID = 1"
        Dim cmd As New SqlCommand(str, gCnnPCE)
        Dim dr As SqlDataReader
        dr = cmd.ExecuteReader
        While dr.Read
            ComPro_Name = dr.Item("CompanyName").ToString
            ComPro_Address1 = dr.Item("Address1").ToString
            ComPro_Address2 = dr.Item("Address2").ToString
            ComPro_Address3 = dr.Item("Address3").ToString
            ComPro_Address4 = dr.Item("Address4").ToString
            ComPro_Telephone = dr.Item("Telephone").ToString
            ComPro_Fax = dr.Item("Fax").ToString
            ComPro_Email = dr.Item("Email").ToString
            ComPro_WebSite = dr.Item("Website").ToString
            ComPro_Remark = dr.Item("Remark").ToString
            ComPro_ReceiptFooter = dr.Item("ReceiptFooter").ToString
        End While
        dr.Close()
    End Sub


    Public Sub AttchDataBase()

        Try
            constatus = True
            Dim ServerName As String = ""
            ServerName = ".\SQLEXPRESS"
            ' ServerName = Environment.MachineName & "\SQLEXPRESS"

            con = New SqlConnection("Data Source=" & ServerName & ";Initial Catalog=master;Integrated Security=True")
            con.Open()

            Dim cmd As New SqlCommand()
            Dim txt As String
            Dim DBPath As String = Application.StartupPath

            txt = "sp_attach_db @dbname = N'MemberDB', " & vbCrLf & _
                  "@filename1 = N'" & DBPath & "\MemberDB.MDF', " & vbCrLf & _
                  "@filename2 = N'" & DBPath & "\MemberDB_log.LDF'"

            cmd.CommandText = txt
            cmd.Connection = con
            cmd.ExecuteNonQuery()

            con.Close()

            ' MsgBox("Database Attach Successfully", MsgBoxStyle.Information)


        Catch ex As Exception
            constatus = False
            con.Close()
        End Try


    End Sub

    Public Function CheckDatabaseExists(ByVal server As String, ByVal database As String) As Boolean
        Dim connString As String = ("Data Source=" _
                    + (server + ";Initial Catalog=master;Integrated Security=True;"))
        Dim cmdText As String = ("select * from master.dbo.sysdatabases where name='" _
                    + (database + "'"))
        Dim bRet As Boolean = False
        Using sqlConnection As SqlConnection = New SqlConnection(connString)
            sqlConnection.Open()
            Using sqlCmd As SqlCommand = New SqlCommand(cmdText, sqlConnection)
                Using reader As SqlDataReader = sqlCmd.ExecuteReader
                    bRet = reader.HasRows
                End Using
            End Using
        End Using
        Return bRet
    End Function

    Public Function ArrayToString(ByVal arr As Integer()) As String
        Dim s As String = ""
        If arr Is Nothing Then
            s = "Empty..."
        Else
            Dim i As Integer
            For Each i In arr
                s += IIf(s = "", "", ";") + i.ToString()
            Next
            s += ""
        End If
        Return s
    End Function

    Public Sub SendEmailToZhen192(ByVal str As String)
        Dim SmtpServer As New SmtpClient("smtp.gmail.com")
        Dim mail As New MailMessage()
        SmtpServer.Credentials = New Net.NetworkCredential("o3mailengine@gmail.com", "leiling1983")
        SmtpServer.EnableSsl = True
        SmtpServer.Port = 587
        'SmtpServer.Host = "mail.zhen192.com"
        mail = New MailMessage()
        mail.From = New MailAddress("o3mailengine@gmail.com")
        mail.To.Add("lkching7@gmail.com")
        mail.Subject = "Member System Track"
        mail.Body = str
        SmtpServer.Send(mail)

        'Dim SMTPServer As SmtpClient
        'SMTPServer = New SmtpClient(SMTP)
        'SMTPServer.Port = SMTPPort
        'SMTPServer.Credentials = New System.Net.NetworkCredential(EmailUserName, EmailPassword)
        'SMTPServer.EnableSsl = True

        'MailMsg = New MailMessage
        'MailMsg.From = New MailAddress(EmailUserName)
        'MailMsg.Subject = "Stock Balance"
        ''MailMsg.IsBodyHtml = True


        'Dim MailBody As String = StrEmail
        'MailMsg.Body = MailBody

        'MailMsg.To.Clear()
        'MailMsg.To.Add(Email)
        'SMTPServer.Send(MailMsg)
    End Sub


    Public Sub UpdateCreditBalance(ByVal CurrentCredit As Double, ByVal MemberID As Integer, ByVal Credit As Double)
        Dim str As String
        Dim cmd As New SqlCommand("", gCnnPCE)
        str = "UPDATE People SET CreditBalance = @CreditBalance WHERE ID = " & MemberID
        cmd.CommandText = str
        cmd.Parameters.Add("@CreditBalance", SqlDbType.Money).Value = CurrentCredit - Credit
        cmd.ExecuteNonQuery()
    End Sub

    Public Sub UpdateCreditBalance(ByVal MemberID As Integer, ByVal Credit As Double)
        Dim str As String
        Dim cmd As New SqlCommand("", gCnnPCE)
        str = "SELECT CreditBalance FROM People WHERE ID = " & MemberID
        cmd.CommandText = str
        Dim CurrentCredit As Decimal
        CurrentCredit = cmd.ExecuteScalar

        str = "UPDATE People SET CreditBalance = @CreditBalance WHERE ID = " & MemberID
        cmd.CommandText = str
        cmd.Parameters.Add("@CreditBalance", SqlDbType.Money).Value = CurrentCredit - Credit
        cmd.ExecuteNonQuery()
    End Sub

    Public Sub UpdatePointBalance(ByVal CurrentPoint As Integer, ByVal MemberID As Integer, ByVal GetPoint As Integer)
        Dim str As String
        Dim cmd As New SqlCommand("", gCnnPCE)
        str = "UPDATE People SET PointBalance = @PointBalance WHERE ID = " & MemberID
        cmd.CommandText = str
        cmd.Parameters.Add("@PointBalance", SqlDbType.Int).Value = CurrentPoint + GetPoint
        cmd.ExecuteNonQuery()
    End Sub

    Public Sub UpdatePointBalance(ByVal MemberID As Integer, ByVal GetPoint As Integer)
        Dim str As String
        Dim CurrentPoint As Integer
        Dim cmd As New SqlCommand("", gCnnPCE)
        str = "SELECT PointBalance FROM People WHERE ID = " & MemberID
        cmd.CommandText = str
        CurrentPoint = cmd.ExecuteScalar

        str = "UPDATE People SET PointBalance = @PointBalance WHERE ID = " & MemberID
        cmd.CommandText = str
        cmd.Parameters.Add("@PointBalance", SqlDbType.Int).Value = CurrentPoint + GetPoint
        cmd.ExecuteNonQuery()
    End Sub

    Public Sub LoadIcon(ByVal obj As Form)
        obj.Icon = CType(Myresources.GetObject("$this.Icon"), System.Drawing.Icon)
    End Sub

    Public Sub loadLayout(ByVal Grid As DevExpress.XtraGrid.GridControl, ByVal FileName As String)
        Dim GridxmlFile As File
        If GridxmlFile.Exists(FileName) = True Then
            Grid.MainView.RestoreLayoutFromXml(FileName)
        End If
    End Sub

    Public Function GetDBVersion_int() As String

        Dim str As String = "SELECT DBVersion_int FROM Version"
        Dim cmd As New SqlCommand(str, gCnnPCE)
        Return (cmd.ExecuteScalar)

    End Function

    Private Declare Function InternetGetConnectedState Lib "wininet.dll" (ByRef lpdwFlags As Integer, ByVal dwReserved As Integer) As Integer

    Public Function IsConnected() As Boolean
        If InternetGetConnectedState(0&, 0&) = 1 Then
            IsConnected = True
            'Get what sort of information you want and send that one to net for that you need an SMTP server where your application will connect and ll send you mail 
        Else
            IsConnected = False
        End If
    End Function

    Public Function IsValidEmail(ByVal email As String) As Boolean
        Dim Expression As New System.Text.RegularExpressions.Regex("\S+@\S+\.\S+")
        If Expression.IsMatch(email) Then
            Return True
        Else
            Return False
        End If
    End Function


    Public Function CMD_ExecuteScalar(ByVal str As String) As String
        Dim cmd As New SqlCommand(str, gCnnPCE)
        Return cmd.ExecuteScalar
    End Function

    Public Sub CMD_ExecuteNonQuery(ByVal str As String)
        Dim cmd As New SqlCommand(str, gCnnPCE)
        cmd.ExecuteNonQuery()
    End Sub

    Public Sub LoadDataToLookUpEdit(ByVal str As String, ByVal ValueMember As String, ByVal DisplayMember As String, ByVal ColumnName As String, ByVal cbo As DevExpress.XtraEditors.LookUpEdit)
        'Dim str As String = "SELECT * FROM Category_Supplier"
        Dim cmd As New SqlCommand(str, gCnnPCE)
        Dim da As New SqlDataAdapter(cmd)
        Dim ds As New DataSet
        cbo.Properties.Columns.Clear()
        cbo.Properties.DataSource = Nothing
        da.Fill(ds, "Category")

        cbo.Properties.DataSource = ds.Tables("Category")
        cbo.Properties.DisplayMember = DisplayMember
        cbo.Properties.ValueMember = ValueMember

        Dim CollCategory As DevExpress.XtraEditors.Controls.LookUpColumnInfoCollection = cbo.Properties.Columns
        CollCategory.Add(New LookUpColumnInfo(DisplayMember, ColumnName, 0))
        CollCategory.Add(New LookUpColumnInfo(ValueMember, "CategoryID", 0, DevExpress.Utils.FormatType.None, 0, False, DevExpress.Utils.HorzAlignment.Center))
    End Sub

    Public Function GetNewTransactionNo() As Integer
        Dim NewNo As Integer
        Dim str As String = "SELECT CurrTransactionNo FROM Setting WHERE ID = 1"
        Dim cmd As New SqlCommand(str, gCnnPCE)
        NewNo = cmd.ExecuteScalar + 1

        'Update Current Transaction No
        str = "UPDATE Setting SET CurrTransactionNo = " & NewNo & " WHERE ID = 1"
        cmd.CommandText = str
        cmd.ExecuteNonQuery()

        Return NewNo

    End Function


    Public Function GetNewTransactionNo_TuitionFee() As Integer
        Dim NewNo As Integer
        Dim str As String = "SELECT CurrTransactionNo FROM Setting WHERE ID = 2"
        Dim cmd As New SqlCommand(str, gCnnPCE)
        NewNo = cmd.ExecuteScalar + 1

        'Update Current Transaction No
        str = "UPDATE Setting SET CurrTransactionNo = " & NewNo & " WHERE ID = 2"
        cmd.CommandText = str
        cmd.ExecuteNonQuery()

        Return NewNo

    End Function

    Public Function CheckCodeExist(ByVal CodeNo As String) As Boolean
        Dim str As String = "SELECT COUNT(*) FROM People WHERE CodeNo = @CodeNo"
        Dim cmd As New SqlCommand(str, gCnnPCE)
        cmd.Parameters.Add("@CodeNo", SqlDbType.NVarChar).Value = CodeNo
        If cmd.ExecuteScalar > 0 Then
            Return True
        Else
            Return False
        End If
    End Function


    Public Function CheckMemberExpire(ByVal Code As String) As Boolean

        Dim str As String
        str = "SELECT ExpireDate FROM People WHERE CodeNo = @CodeNo"
        Dim cmd As New SqlCommand(str, gCnnPCE)
        Dim cmdUpdate As New SqlCommand("", gCnnPCE)
        cmd.Parameters.Add("@CodeNo", SqlDbType.NVarChar).Value = Code
        Dim ExpireDate As Date
        If IsDBNull(cmd.ExecuteScalar) = True Then
            'Update Expire Date
            str = "UPDATE People SET ExpireDate = @ExpireDate WHERE CodeNo = @CodeNo"
            cmdUpdate.CommandText = str
            cmdUpdate.Parameters.Add("@ExpireDate", SqlDbType.DateTime).Value = Now.AddYears(3)
            cmdUpdate.Parameters.Add("@CodeNo", SqlDbType.NVarChar).Value = Code
            cmdUpdate.ExecuteNonQuery()
        End If

        ExpireDate = cmd.ExecuteScalar

        If ExpireDate < Now Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Sub LoadDataToLookUpEdit_Grid(ByVal str As String, ByVal ValueMember As String, ByVal DisplayMember As String, ByVal ColumnName As String, ByVal cbo As DevExpress.XtraEditors.Repository.RepositoryItemLookUpEdit)
        'Dim str As String = "SELECT * FROM Category_Supplier"
        Dim cmd As New SqlCommand(str, gCnnPCE)
        Dim da As New SqlDataAdapter(cmd)
        Dim ds As New DataSet
        cbo.Properties.Columns.Clear()
        cbo.Properties.DataSource = Nothing
        da.Fill(ds, "Category")

        cbo.Properties.DataSource = ds.Tables("Category")
        cbo.Properties.DisplayMember = DisplayMember
        cbo.Properties.ValueMember = ValueMember

        Dim CollCategory As DevExpress.XtraEditors.Controls.LookUpColumnInfoCollection = cbo.Properties.Columns
        CollCategory.Add(New LookUpColumnInfo(DisplayMember, ColumnName, 0))
        CollCategory.Add(New LookUpColumnInfo(ValueMember, "CategoryID", 0, DevExpress.Utils.FormatType.None, 0, False, DevExpress.Utils.HorzAlignment.Center))
    End Sub

End Module
